﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BLL.Services.Magazines.Listing;

namespace BLL.Services.Magazines
{
    public class MagazineCollection
    {
        private readonly string _connectionString;

        public MagazineCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Create(Magazine magazine)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Magazines.Insert(conn, magazine.Name);

                cmd.ExecuteNonQuery();
            }
        }

        public void Save(Magazine magazine)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Magazines.Update(conn,magazine.Name,magazine.MagazineId);

                cmd.ExecuteNonQuery();

            }
        }

        public void Delete(int magazineId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Magazines.Delete(conn, magazineId);

                cmd.ExecuteNonQuery();
            }
        }

        public Magazine GetMagazineById(int magazineId)
        {
            var magazine = new Magazine();
            using (var conn  = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Magazines.GetById(conn, magazineId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        magazine = new Magazine
                        {
                            MagazineId = reader.GetInt32(0),
                            Name = reader[1] as string
                        };


                    }
                    return magazine;
                }
            }
        }

        public MagazinesListing Listing(int page, int size, string column, int direction, MagazinesFilter filter)
        {
            var magazonesListing = new MagazinesListing();

            if (filter == null)
            {
                filter = new MagazinesFilter();
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                magazonesListing.Column = column;
                magazonesListing.Direction = direction;
                magazonesListing.Filter = filter;
                magazonesListing.Page = page;
                magazonesListing.Size = size;

                var cmd = StoredProcedures.Magazines.Listing(conn, page, size, column, direction, filter.Name);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    {
                        Direction = ParameterDirection.ReturnValue
                    };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        magazonesListing.Magazines.Add(new MagazinesListing.Magazine
                            {
                                MagazineId = reader.GetInt32(0),
                                Name = reader[1] as string
                            });
                    }
                }

                magazonesListing.TotalPages = (int)totalCount.Value % size == 0
                                                  ? (int)totalCount.Value / size
                                                  : ((int)totalCount.Value / size) + 1;
            }
            return magazonesListing;
        }

    }
}
